package com.dx.mes.service.spc.service;

import com.dx.common.table.ppt.Bis_m_item;
import com.dx.common.table.ppt.Spc_ret_mfg_data_zr;
import com.dx.mes.common.dao.IBisMItemDao;
import com.dx.mes.common.dao.ISpcRetMfgDataZrDao;
import com.dx.mes.common.transcoder.util.AppContext;
import com.dx.mes.common.transcoder.util.JacksonUtil;
import com.dx.mes.common.transcoder.util.LogUtils;
import com.dx.mes.service.spc.tx.Xpcmsdt.XpcmsrdtI;
import com.dx.mes.service.spc.tx.Xpcmsdt.XpcmsrdtIA;
import com.dx.mes.service.spc.tx.Xpcmsdt.XpcmsrdtO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import static com.dx.mes.common.bean.def.GenericDef.RETURN_CODE_OK;

/**
 * Created by XXD on 2018/6/12.
 */
@Service
//@PropertySource("classpath:filePaths.properties")
public class TimingParseService {
    private LogUtils logUtils;
    @Autowired
    ISpcRetMfgDataZrDao spcRetMfgDataZrDao;
    @Autowired
    private IFqprepdataService fqprepdataService;
    @Autowired
    private IBisMItemDao bisMItemDao;


    //jdbc连接sqlserver数据库
    public Connection connectSQLserver(String Url,String Username,String Password){
        Connection conn = null;
        try{
        // 加载数据库驱动，注册到驱动管理器
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        // 数据库连接字符串
        String url = Url;
        // 数据库用户名
        String username = Username;
        // 数据库密码
        String password = Password;
        // 创建Connection连接
        conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //先查edc表中最新的一笔数据的时间
    public List<Spc_ret_mfg_data_zr> findLastTimeRptTime(String toolId){
        String hql = "From Spc_ret_mfg_data_zr where tool_id_fk ='" + toolId + "'";
        hql += " and mes_id_fk = '" + "ZRAUTOQZ" + "'";
        hql += " order by rpt_timestamp desc";
        List<Spc_ret_mfg_data_zr> spcRetMfgDataZrList = spcRetMfgDataZrDao.find(hql);
        return spcRetMfgDataZrList;
    }

    //查配置表
    public List<Bis_m_item> queryItems(String tool_id_fk, String mes_id_fk) {
        String hql2 = "From Bis_m_item where tool_id_fk = '" + tool_id_fk+"'";
        hql2 += " and mes_id_fk = '" + mes_id_fk +"'";
        hql2 += " order by data_seq";
        List<Bis_m_item> bis_m_items = bisMItemDao.find(hql2);
        return bis_m_items;
    }


    //组trx
    public XpcmsrdtI sendTrx(ResultSet rs,String dataGroup,String toolId,String fabId,String line,String opeId)throws Exception{
        // 实例化Book对象
        XpcmsrdtI trxI = new XpcmsrdtI();
        List<XpcmsrdtIA> iaryList = new ArrayList<>();
        XpcmsrdtIA iary = new XpcmsrdtIA();

        //量测名称
        iary.setData_group(dataGroup);
        iary.setData_value(String.valueOf(rs.getFloat("减重(g)")));
        iary.setData_group_seq("1");
        iary.setData_type("03");
        iaryList.add(iary);

        Timestamp timestamp  = rs.getTimestamp("时间");
        trxI.setIary(iaryList);
        trxI.setTool_id(toolId);
        trxI.setFabId(fabId);
        trxI.setLineId(line);
        trxI.setOpe_id(opeId);
        trxI.setRep_unit_fk("M");//上报单元 P:process M:messure
        trxI.setData_pat_fk("00");//上报属性 00,01,02,03   00:EDC
        trxI.setMes_id_fk("ZRAUTOQZ");
        trxI.setData_cnt(String.valueOf(iaryList.size()));
        trxI.setIary(iaryList);
        trxI.setFlag("A");
        //TODO
        trxI.setEvt_timestamp(timestamp);//量测时间
        return trxI;
    }


    //定时读取制绒线的sqlserver数据库
    public void readZRserver(){
        logUtils = new LogUtils(TimingParseService.class);
        logUtils.info("开始捞sqlserver数据！");
        SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dataGroup="去重";
        String toolId="TEXT03";
        String fabId="电池厂";
        String line="101A-1";
        String opeId="制绒";
        List<Spc_ret_mfg_data_zr>  spcRetMfgDataZrList = findLastTimeRptTime(toolId);
        Timestamp lastTimestap = null;
        List<Bis_m_item> bisMItemList = queryItems(toolId,"ZRAUTOQZ");
        //未设置配置项则不做任何操作
        if(bisMItemList.size() == 0){
            return;
        }

        Connection conn = null;
        String url = "jdbc:sqlserver://10.0.16.24:1433;databaseName=robo_data_base_6";
        String user = "mes";
        String password = "123456";
        conn = connectSQLserver(url,user,password);
        //TODO 判断连接有没有成功
        try{
            logUtils.error(conn.toString());
            logUtils.info(conn.toString());
            // 获取Statement
            Statement stmt = conn.createStatement();
            // 添加SQL语句
            String sql = "select *  FROM table_weight";
            // 执行查询
            ResultSet rs = stmt.executeQuery(sql);
            // 实例化List对象
            List<XpcmsrdtI> xpcmsrdtIList = new ArrayList<XpcmsrdtI>();
            if(spcRetMfgDataZrList != null && spcRetMfgDataZrList.size() > 0 ){
                lastTimestap  = spcRetMfgDataZrList.get(0).getRpt_timestamp();
                // 判断光标向后移动，并判断是否有效
                while (rs.next()) {
                    Timestamp tableTimestamp  = rs.getTimestamp("时间");
                    //如果sqlserver时间小于 最后上报的时间，则过滤掉
                    if (tableTimestamp.compareTo(lastTimestap) <= 0) {
                        continue;
                    }
                    XpcmsrdtI trx =  sendTrx(rs,dataGroup,toolId,fabId,line,opeId);
                    xpcmsrdtIList.add(trx);
                }
            }else{
                while (rs.next()) {
                //全存
                Timestamp tableTimestamp  = rs.getTimestamp("时间");
                logUtils.info("时间:------------" +tableTimestamp.toString());
                //yyyy-MM-dd HH:mm:ss
                String midtime = "2018-06-13 12:00:00";
                Timestamp ts = Timestamp.valueOf(midtime);
                if(tableTimestamp.compareTo(ts) > 0){
                        XpcmsrdtI trx =  sendTrx(rs,dataGroup,toolId,fabId,line,opeId);
                        xpcmsrdtIList.add(trx);
                    }
                }
            }

            //循环送service 单点上报
            if(xpcmsrdtIList.size() > 0 ){
                logUtils.info("单点上报！！！！");
                for(int i = 0; i < xpcmsrdtIList.size();i++){
                    XpcmsrdtO xpcmsrdtO = new XpcmsrdtO();
                    xpcmsrdtO =  autoReportSPC(xpcmsrdtIList.get(i));
                    if (!xpcmsrdtO.getRtn_code().equals(RETURN_CODE_OK)) {
                        logUtils.error(xpcmsrdtO.getRtn_mesg());
                        break;
                    }
                }
            }
        }catch (Exception e){
            logUtils.error(e.toString());
        }

    }


    //上报spc
    private XpcmsrdtO autoReportSPC(XpcmsrdtI inTrx) throws Exception {
        XpcmsrdtI xpcmsrdtI = inTrx;
        XpcmsrdtO xpcmsrdtO = new XpcmsrdtO();
        xpcmsrdtO.setTrx_id("FQPEDCDAT");
        xpcmsrdtO.setTrx_id("O");

        String strOutTrx = fqprepdataService.subMainProc(AppContext.getCurrEventNumber(),
                JacksonUtil.toJSONStr(xpcmsrdtI));
        xpcmsrdtO = JacksonUtil.fromJson(strOutTrx, XpcmsrdtO.class);
        return xpcmsrdtO;
    }

}
